import mysql, {type ResultSetHeader} from 'mysql2/promise';
import {DB_USER, DB_PASSWORD, DB_DATABASE} from 'astro:env/server'

const db_holder = {
    pool: null as mysql.Pool | null
}

function getPool() {
    if (!db_holder.pool) {
        defineDb({
            user: 'chuanqir_tyl',
            password: 'tang-yl#1',
            host: 'localhost',
            database: 'chuanqir_blog'
        })
    }
    return db_holder.pool as mysql.Pool;
}

export const defineDb = ({user, database, password, host}: {
    user: string,
    password: string,
    database: string,
    host: string
}) => {
    let pool = mysql.createPool({
        host,
        user,
        database,
        password,
        waitForConnections: true,
        connectionLimit: 5,
        maxIdle: 5, // max idle connections, the default value is the same as `connectionLimit`
        idleTimeout: 60000, // idle connections timeout, in milliseconds, the default value 60000
        queueLimit: 0,
        enableKeepAlive: true,
        keepAliveInitialDelay: 0,
    });
    if (pool) {
        db_holder.pool = pool;
    }
}

export const queryList = async (sql: string, values?: any[]) => {
    const [rows] = await getPool().query(sql, values);
    return rows as any[];
}
export const queryOne = async (sql: string, values?: any[]) => {
    const [rows] = await getPool().query(sql, values);
    let res = rows as any[]
    if (res.length === 1) {
        return res[0];
    } else if (res.length > 1) {
        throw new Error('database queryOne error');
    } else {
        return null
    }
}

export const update = async (sql: string, values?: any[]) => {
    const [result] = await getPool().query(sql, values);
    return result as ResultSetHeader;
}
export default {
    defineDb,
}